-- Active: 1713856700339@@127.0.0.1@5432@adonis@public
create or replace
function in_out_report(
  start_date VARCHAR(10),
  end_date VARCHAR(10)
)
returns table (
  warehouse_id INT,
  warehouse_name VARCHAR(30),
  goods_id INT,
  goods_name VARCHAR(100),
  goods_spec VARCHAR(100),
  fk_category_id INT,
  category_name VARCHAR(60),
  start_qty FLOAT,
  start_amt FLOAT,
  in_qty FLOAT,
  in_amt FLOAT,
  out_qty FLOAT,
  out_amt FLOAT,
  end_qty FLOAT,
  end_amt FLOAT
) as $$
begin
    drop table if exists t_remain_stock;

drop table if exists t_out_stock;

create temp table t_remain_stock (
        warehouse_id INT null,
        warehouse_name varchar(30) null,
        goods_id INT null,
        goods_name varchar(100) null,
        goods_spec varchar(100),
        fk_category_id INT,
        category_name varchar(60),
        start_qty float default 0,
        start_amt float default 0,
        in_qty float default 0,
        in_amt float default 0,
        out_qty float default 0,
        out_amt float default 0,
        end_qty float default 0,
        end_amt float default 0
    );

create temp table t_out_stock (
        goods_id INT null,
        warehouse_id INT null,
        unit json null,
        quantity float null,
        amt float null
    );

insert
	into
	t_remain_stock (goods_id,
	warehouse_id,
	start_qty,
	start_amt)
    select
	fk_goods_id,
	-- 商品ID
	fk_warehouse_id,
	-- 仓库ID
	SUM(quantity),
	-- 总数量
	SUM(unit_price)
	-- 总金额
from
	stock_in_details
	-- where billDate < start_date
group by
	fk_goods_id,
	fk_warehouse_id;

insert
	into
	t_out_stock (goods_id,
	warehouse_id,
	quantity,
	amt)
    select
	fk_goods_id,
	-- 商品ID
	fk_warehouse_id,
	-- 仓库ID
	sum(quantity),
	-- 总数量
	sum(unit_price)
	-- 总金额
from
	stock_out_details
	-- where billDate < start_date
group by
	fk_goods_id,
	fk_warehouse_id;

update
	t_remain_stock t
set
	start_qty = t.start_qty - a.quantity
from
	t_out_stock a
where
	a.goods_id = t.goods_id
	and a.warehouse_id = t.warehouse_id;

delete
from
	t_out_stock a
		using t_remain_stock t
where
	a.goods_id = t.goods_id
	and a.warehouse_id = t.warehouse_id;

insert
	into
	t_remain_stock (goods_id,
	warehouse_id,
	start_qty,
	start_amt)
    select
	t.goods_id,
	t.warehouse_id,
	0 - t.quantity,
	-- 或者使用 quantity - 0 作为初始值
	t.amt
from
	t_out_stock t;

truncate
	t_out_stock;
-- 入库逻辑
    insert
	into
	t_out_stock (goods_id,
	warehouse_id,
	quantity,
	amt)
    select
	fk_goods_id,
	fk_warehouse_id,
	sum(quantity),
	sum(unit_price)
from
	stock_in_details
	-- where billDate >= start_date
	-- and  billDate <= end_date
group by
	fk_goods_id,
	fk_warehouse_id;

update
	t_remain_stock as t
    set
	out_qty = a.quantity,
	out_amt = a.amt
from
	t_out_stock as a
where
	a.goods_id = t.goods_id
	and a.warehouse_id = t.warehouse_id;

delete
from
	t_out_stock
		using t_out_stock a
inner join t_remain_stock t
    on
	a.goods_id = t.goods_id
	and a.warehouse_id = t.warehouse_id;

insert
	into
	t_remain_stock (
        goods_id,
	warehouse_id,
	start_qty,
	start_amt,
	in_qty,
	in_amt,
	out_qty,
	out_amt
    )
    select
	t.goods_id,
	t.warehouse_id,
	0,
	0,
	0,
	0,
	t.quantity,
	t.amt
from
	t_out_stock t;

update
	t_remain_stock trs
set
	end_qty = trs.start_qty + trs.in_qty - trs.out_qty,
	end_amt = trs.start_amt + trs.in_amt - trs.out_amt;

update
	t_remain_stock a
set
	goods_name = b.name,
	fk_category_id = b.fk_category_id,
	category_name = c.name,
	goods_spec = b.spec
from
	goods b
left join dicts c on
	b.fk_category_id = c.id
where
	a.goods_id = b.id;

update
	t_remain_stock a
set
	warehouse_name = b.name
from
	dicts b
where
	a.warehouse_id = b.id
	and b.class_name = 'warehouse';

return QUERY
select
	*
from
	t_remain_stock;
end;

$$ language plpgsql;
